package com.dao.impl;

import com.dao.IFlightManageDao;
import com.model.FlightModel;
import com.utils.JdbcUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.ArrayList;
import java.util.List;

public class FlightManageDaoImpl implements IFlightManageDao {
    private JdbcTemplate jt = new JdbcTemplate(JdbcUtils.getDs());

    @Override
    public List<FlightModel> getFlightList(int offset, int size, String query) throws Exception {
        StringBuilder sql = new StringBuilder("SELECT * FROM flights WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (query != null && !query.trim().isEmpty()) {
            sql.append(" AND (flight_number LIKE ? OR departure_city LIKE ? OR arrival_city LIKE ?)");
            String likeQuery = "%" + query + "%";
            params.add(likeQuery);
            params.add(likeQuery);
            params.add(likeQuery);
        }

        sql.append(" ORDER BY departure_time DESC LIMIT ?, ?");
        params.add(offset);
        params.add(size);

        return jt.query(sql.toString(), params.toArray(), new BeanPropertyRowMapper<>(FlightModel.class));
    }

    @Override
    public int getFlightCount(String query) throws Exception {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM flights WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (query != null && !query.trim().isEmpty()) {
            sql.append(" AND (flight_number LIKE ? OR departure_city LIKE ? OR arrival_city LIKE ?)");
            String likeQuery = "%" + query + "%";
            params.add(likeQuery);
            params.add(likeQuery);
            params.add(likeQuery);
        }

        return jt.queryForObject(sql.toString(), params.toArray(), Integer.class);
    }

    @Override
    public FlightModel createFlight(FlightModel flight) throws Exception {
        String sql = "INSERT INTO flights (flight_number, departure_city, arrival_city, departure_time, " +
                    "arrival_time, aircraft_type, flight_duration, status) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        
        jt.update(sql,
            flight.getFlightNumber(),
            flight.getDepartureCity(),
            flight.getArrivalCity(),
            flight.getDepartureTime(),
            flight.getArrivalTime(),
            flight.getAircraftType(),
            flight.getFlightDuration(),
            flight.getStatus()
        );
        
        // 获取新创建的航班ID
        Integer flightId = jt.queryForObject("SELECT LAST_INSERT_ID()", Integer.class);
        
        // 插入票价信息
        if (flight.getTickets() != null && !flight.getTickets().isEmpty()) {
            String ticketSql = "INSERT INTO tickets (flight_id, ticket_type, price, quantity) VALUES (?, ?, ?, ?)";
            for (Map<String, Object> ticket : flight.getTickets()) {
                jt.update(ticketSql,
                    flightId,
                    ticket.get("type"),
                    ticket.get("price"),
                    ticket.get("quantity")
                );
            }
        }
        
        return getFlightById(flightId);
    }

    @Override
    public FlightModel updateFlight(FlightModel flight) throws Exception {
        String sql = "UPDATE flights SET flight_number=?, departure_city=?, arrival_city=?, " +
                    "departure_time=?, arrival_time=?, aircraft_type=?, flight_duration=? " +
                    "WHERE flight_id=?";
        
        jt.update(sql,
            flight.getFlightNumber(),
            flight.getDepartureCity(),
            flight.getArrivalCity(),
            flight.getDepartureTime(),
            flight.getArrivalTime(),
            flight.getAircraftType(),
            flight.getFlightDuration(),
            flight.getFlightId()
        );
        
        // 更新票价信息
        if (flight.getTickets() != null && !flight.getTickets().isEmpty()) {
            // 先删除原有票价
            jt.update("DELETE FROM tickets WHERE flight_id=?", flight.getFlightId());
            
            // 插入新票价
            String ticketSql = "INSERT INTO tickets (flight_id, ticket_type, price, quantity) VALUES (?, ?, ?, ?)";
            for (Map<String, Object> ticket : flight.getTickets()) {
                jt.update(ticketSql,
                    flight.getFlightId(),
                    ticket.get("type"),
                    ticket.get("price"),
                    ticket.get("quantity")
                );
            }
        }
        
        return getFlightById(flight.getFlightId());
    }

    @Override
    public void deleteFlight(Integer flightId) throws Exception {
        // 先删除相关的票价信息
        jt.update("DELETE FROM tickets WHERE flight_id=?", flightId);
        // 再删除航班信息
        jt.update("DELETE FROM flights WHERE flight_id=?", flightId);
    }

    @Override
    public void updateFlightStatus(Integer flightId, String status) throws Exception {
        String sql = "UPDATE flights SET status=? WHERE flight_id=?";
        jt.update(sql, status, flightId);
    }

    private FlightModel getFlightById(Integer flightId) throws Exception {
        String sql = "SELECT * FROM flights WHERE flight_id=?";
        FlightModel flight = jt.queryForObject(sql, new BeanPropertyRowMapper<>(FlightModel.class), flightId);
        
        // 获取票价信息
        String ticketSql = "SELECT * FROM tickets WHERE flight_id=?";
        List<Map<String, Object>> tickets = jt.queryForList(ticketSql, flightId);
        flight.setTickets(tickets);
        
        return flight;
    }
} 